﻿using DAL;
using Model;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace MainOP
{
    public partial class FormDataSelectCall : Form
    {
        public FormDataSelectCall()
        {
            InitializeComponent();
        }
        List<ProductData> objProductData = new List<ProductData>();
        private void button1_Click(object sender, EventArgs e)
        {
            //查询前先清空数据集
            objProductData.Clear();
            //清空表格再查询
            while (this.dataGridView1.Rows.Count != 1)
            {
                this.dataGridView1.DataSource = null;
            }
            string sql = "select * from calRecord where [callDateTime] Between '{0}' and '{1}' order by [callDateTime] ASC";
            sql = string.Format(sql, Convert.ToDateTime(dateTimePicker1.Text), Convert.ToDateTime(dateTimePicker2.Text));
            SqlDataReader objReader = SQLHelp.GetReader(sql);
            //循环写入数据
            while (objReader.Read())
            {
                ProductData productData = new ProductData();
                try
                {
                    productData.callDateTime = Convert.ToDateTime(objReader["callDateTime"]);
                }
                catch
                { }
                productData.CallStation = objReader["CallStation"].ToString();
                productData.CallOP = objReader["CallOP"].ToString();
                productData.CallOptions = objReader["CallOptions"].ToString();
                productData.CallTimers = objReader["CallTimers"].ToString();
                objProductData.Add(productData);
            }

            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.DataSource = objProductData;
            objReader.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //查询前先清空数据集
            objProductData.Clear();
            //清空表格再查询
            while (this.dataGridView1.Rows.Count != 1)
            {
                this.dataGridView1.DataSource = null;
            }
            string sql;
            if (comboBox1.Text == "")
            {
                sql = "select * from calRecord where concat([callStation], [callOP], [callOptions],[callTimers]) like '%{0}%'";
                sql = string.Format(sql, textBox1.Text);
            }
            else
            {
                sql = "select * from calRecord where " + comboBox1.Text + "='" + textBox1.Text + "'" ;
            }
            SqlDataReader objReader = SQLHelp.GetReader(sql);
            //循环写入数据
            while (objReader.Read())
            {
                ProductData productData = new ProductData();
                productData.CallStation = objReader["CallStation"].ToString();
                productData.CallOP = objReader["CallOP"].ToString();
                productData.CallOptions = objReader["CallOptions"].ToString();
                productData.CallTimers = objReader["CallTimers"].ToString();
                objProductData.Add(productData);
            }
            this.dataGridView1.AutoGenerateColumns = false;
            this.dataGridView1.DataSource = objProductData;
            objReader.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //创建Excel工作簿和工作表
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet newsheet = workbook.CreateSheet("呼叫记录");

            //创建Excel工作表的列标题
            IRow rowHeader = newsheet.CreateRow(0);
            rowHeader.CreateCell(0, CellType.String).SetCellValue("日期时间");
            rowHeader.CreateCell(1, CellType.String).SetCellValue("呼叫工位");
            rowHeader.CreateCell(2, CellType.String).SetCellValue("员工号");
            rowHeader.CreateCell(3, CellType.String).SetCellValue("呼叫内容");
            rowHeader.CreateCell(4, CellType.String).SetCellValue("持续时间");
            
            //循环创建Excel行，并填充数据
            for (int i = 0; i < objProductData.Count; i++)
            {
                IRow row = newsheet.CreateRow(i + 1);
                row.CreateCell(0, CellType.String).SetCellValue(objProductData[i].callDateTime);
                row.CreateCell(1, CellType.String).SetCellValue(objProductData[i].CallStation);
                row.CreateCell(2, CellType.String).SetCellValue(objProductData[i].CallOP);
                row.CreateCell(3, CellType.String).SetCellValue(objProductData[i].CallOptions);
                row.CreateCell(4, CellType.String).SetCellValue(objProductData[i].CallTimers);
                
            }

            //选择文件位置
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.InitialDirectory = @"C:\Users\SpringRain\Desktop";
            sfd.Title = "请选择要保存的文件";
            sfd.Filter = "Excel表格|*.xlsx";
            sfd.ShowDialog(this);
            string path = sfd.FileName;

            //将输出流写入Excel工作簿
            try
            {
                using (FileStream fSwrite = File.OpenWrite(path))
                {
                    workbook.Write(fSwrite);
                    MessageBox.Show("保存成功");
                }
            }
            catch
            {

            }
        }
    }
}
